package com.zhang.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

class IndexInfo {
    String tableName;
    String indexName;
    boolean isUnique;
    List<String> columnNames;

    public IndexInfo(String tableName, String indexName, boolean isUnique) {
        this.tableName = tableName;
        this.indexName = indexName;
        this.isUnique = isUnique;
        this.columnNames = new ArrayList<>();
    }

    public void addColumnName(String columnName) {
        this.columnNames.add(columnName);
    }

    public String generateIndexSql() {
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE ");
        if (!isUnique) {
            sb.append("UNIQUE ");
        }
        sb.append("INDEX ").append(indexName).append(" ON ").append(tableName).append(" (");
        for (int i = 0; i < columnNames.size(); i++) {
            sb.append(columnNames.get(i));
            if (i < columnNames.size() - 1) {
                sb.append(", ");
            }
        }
        sb.append(");");
        return sb.toString();
    }
}

public class IndexParser {
    public static void main(String[] args) {
        String filePath = "D:\\桌面\\索引.xls";
        Map<String, IndexInfo> indexMap = new HashMap<>();

        try (FileInputStream file = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(file)) {

            Sheet sheet = workbook.getSheetAt(0);
            boolean isHeader = true;
            for (Row row : sheet) {
                if (isHeader) {
                    isHeader = false;
                    continue;
                }
                String tableName = getCellValueAsString(row.getCell(2));
                String indexName = getCellValueAsString(row.getCell(5));
                boolean isUnique = "0".equals(getCellValueAsString(row.getCell(3)));
                String columnName = getCellValueAsString(row.getCell(7));
                int seqInIndex = Integer.parseInt(getCellValueAsString(row.getCell(6)));

                String key = tableName + "_" + indexName;
                IndexInfo indexInfo = indexMap.computeIfAbsent(key, k -> new IndexInfo(tableName, indexName, isUnique));
                // 插入时按照 SEQ_IN_INDEX 排序
                int insertIndex = 0;
                while (insertIndex < indexInfo.columnNames.size() && seqInIndex > Integer.parseInt(getCellValueAsString(sheet.getRow(findRowIndex(sheet, tableName, indexName, indexInfo.columnNames.get(insertIndex))).getCell(6)))) {
                    insertIndex++;
                }
                indexInfo.columnNames.add(insertIndex, columnName);
            }

            for (IndexInfo indexInfo : indexMap.values()) {
                System.out.println(indexInfo.generateIndexSql());
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((int) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    private static int findRowIndex(Sheet sheet, String tableName, String indexName, String columnName) {
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (tableName.equals(getCellValueAsString(row.getCell(2)))
                    && indexName.equals(getCellValueAsString(row.getCell(5)))
                    && columnName.equals(getCellValueAsString(row.getCell(7)))) {
                return i;
            }
        }
        return -1;
    }
}    